<?php

    class excursions_model extends CI_Model {

        private $fields = array ('title', 'guides', 'description', 'excursion_text', 'addition');

        function __construct ()
        {
            parent::__construct ();
        }

        function get_all ()
        {

            if ( isset ( $_POST['eb_starting'] ) )
                $day = $_POST['eb_starting'];
            if ( isset ( $_POST['eb_freetext'] ) )
                $freetext = $_POST['eb_freetext'];

            $order_by = 'adultPrice';
            $direction = 'ASC';
            if ( isset ( $_POST['eb_sort'] ) )
            {

                $eb_sort = $_POST['eb_sort'];

                switch ( $eb_sort )
                {
                    case 'none':
                        $order_by = 'id';
                        $direction = 'ASC';
                        break;

                    case 'name':
                        $order_by = 'title';
                        $direction = 'ASC';
                        break;

                    case 'price':
                        $order_by = 'adultPrice';
                        $direction = 'ASC';
                        break;
                }
            }
            $where = "status = 1";

            $like = '';

            // search start days ( selected by checkboxes )
            if ( isset ( $day ) && $day != 'Any' )
            {

                // for compatibility with old globtour site (old site post string, and new site post array)
                if ( is_array ( $day ) )
                {

                    $like .= " AND (";

                    foreach ( $day as $d ) :
                        $like .= " excursions.startWeekDay LIKE '%$d%' OR";
                    endforeach;

                    $like = substr_replace ( $like, "", -2 );
                    $like .= ")";
                } else
                {
                    $like .= " AND (excursions.startWeekDay LIKE '%$day%')";
                }
            }

            if ( (isset ( $freetext ) and ($freetext != '') ) )
            {
                $like .= " AND (excursions.excursion_text LIKE '%" . $freetext . "%'";
                $like .= " OR ";
                $like .= "excursions.title LIKE '%" . $freetext . "%'";
                $like .= " OR ";
                $like .= "excursions.description LIKE '%" . $freetext . "%')";
            }

            $where .= $like;

            $q = '
            SELECT excursions.*, gallery.path as g_path, pictures.filename as f_name
            FROM excursions
            LEFT JOIN gallery ON excursions.id = gallery.`excursions_id`
            LEFT JOIN pictures ON gallery.ID = pictures.gallery_ID
            AND pictures.ID=
            (
            SELECT pictures.ID
            FROM pictures
            WHERE pictures.gallery_ID = gallery.ID
            ORDER BY pictures.order ASC, pictures.ID ASC
            LIMIT 0, 1  
            )
            WHERE ' . $where . '
            ORDER BY ' . $order_by . ' ' . $direction . '
            ';

            log_message ( 'info', $q );

            $res = $this->db->query ( $q )->result_array ();

            log_message ( 'debug', $this->db->last_query () );

            /* Translate */

            foreach ( $res as $key => $value )
            {
                foreach ( $this->fields as $f )
                {
                    $res[$key][$f] = $this->translate->getArray ( $value[$f], TRUE );
                    if ( $res[$key][$f] == '' )
                        $res[$key][$f] = '-Please translate-';
                }
            }

            return $res;
        }

        function get_excursion ()
        {

            $id = $_GET['excursion_id'];

            $q = '
            SELECT excursions.*, gallery.path as g_path, pictures.filename as f_name
            FROM excursions
            LEFT JOIN gallery ON excursions.id = gallery.`excursions_id`
            LEFT JOIN pictures ON gallery.ID = pictures.gallery_ID
            AND pictures.ID=
            (
            SELECT pictures.ID
            FROM pictures
            WHERE pictures.gallery_ID = gallery.ID
            ORDER BY pictures.order ASC, pictures.ID ASC
            LIMIT 0, 1  
            )
            WHERE excursions.id = ' . $id . ' AND excursions.status = 1
            ';

            $res = $this->db->query ( $q )->row_array ();

            /* Translate */

            foreach ( $this->fields as $f )
            {
                $res[$f] = $this->translate->getArray ( $res[$f], TRUE );
            }

            return $res;
        }

        function get_images ()
        {

            $id = $_GET['excursion_id'];
            $gallery = $this->db->get_where ( 'gallery', array ('excursions_id' => $id) )->row_array ();

            $this->db->order_by ( 'order asc, ID asc' );
            $res = $this->db->get_where ( 'pictures', array ('gallery_ID' => $gallery['ID']) )->result_array ();

            return $res;
        }

        function check_capacity ()
        {

            unset ( $_POST['lang'] );
            $reserved = 0;

            // get excursion capacity
            $excursion = $this->db->get_where ( 'excursions', array ('id' => $_POST['excid']) )->row_array ();
            $capacity = $excursion['capacity'];

            // get number of reservation for this excursion
            $excursions = $this->db->get_where ( 'excursionbooking', array ('excursions_id' => $_POST['excid'], 'date_from' => $_POST['date'], 'status' => 1) )->result_array ();

            foreach ( $excursions as $value )
            {
                $reserved += $value['noperson'];
            }

            $aviabile = $capacity - $reserved;

            if ( $_POST['persons'] <= $aviabile )
            {

                $this->db->insert ( 'book_info', $_POST );
                $book_id = $this->db->insert_id ();

                return array ('available' => 1, 'book_info' => $book_id);
            }
            else
            {
                return array ('available' => 0, 'place_left' => $aviabile);
            }
        }

        function booking_information ()
        {

            return $this->db->where ( 'id', $_GET['bid'] )->get ( 'book_info' )->row_array ();
        }

        function booking_write ()
        {

            $data = array (
                'title' => $_POST['a_title'][0],
                'firstName' => $_POST['a_firstName'][0],
                'lastName' => $_POST['a_lastName'][0],
                'email' => $_POST['email'],
                'phone' => $_POST['phone'],
                'what' => 2
            );

            $this->db->insert ( 'customers', $data );
            $customerid = $this->db->insert_id ();

            $data = array (
                'excursions_id' => $_POST['excid'],
                'customers_id' => $customerid,
                'userid' => '7',
                'date_from' => $_POST['date'],
                'num_of_day' => '1',
                'status' => 0,
                'noadult' => $_POST['noadult'],
                'noch' => $_POST['noch'],
                'noperson' => $_POST['persons'],
                'chprice' => $_POST['chprice'],
                'adultprice' => $_POST['adultprice'],
                'totalprice' => $_POST['totalprice'],
                'source_info' => $_POST['s_b_i'],
                'pickup_location' => $_POST['pickup_location']
            );

            if ( $this->db->insert ( 'excursionbooking', $data ) )
            {
                $newid = $this->db->insert_id ();
                return array ('success' => 'success', 'booking_id' => $newid);
            }
            else
            {
                return array ('success' => 'failed', 'message' => "database error");
            }
        }

        // Razlika je u nazivu promjenjljivih u nosiocu rezervacije
        // Na starom sajtu se pravi lista klijenata
        // Na novom sajtu postoji samo jedan klijent
        // Razlicita su imena
        // Stari sajt
        // 'title' => $_POST['a_title'][0]
        // 'firstName' => $_POST['a_firstName'][0]
        // 'lastName' => $_POST['a_lastName'][0]
        // Novi sajt
        // 'title' => none
        // 'firstName' => $_POST['name'] ( ovdje je i ime i prezime klijenta )
        // 'lastName' => none

        function booking_write_new ()
        {

            $data = array (
                'title' => "",
                'firstName' => $_POST['name'],
                'lastName' => "",
                'email' => $_POST['email'],
                'phone' => $_POST['phone'],
                'what' => 2,
                'town_city' => $_POST['city'],
                'address1' => $_POST['address'],
                'zip_code' => $_POST['zip'],
                'country' => $_POST['country']
            );

            $this->db->insert ( 'customers', $data );
            $customerid = $this->db->insert_id ();

            $data = array (
                'excursions_id' => $_POST['excid'],
                'customers_id' => $customerid,
                'userid' => '7',
                'date_from' => $_POST['date'],
                'num_of_day' => '1',
                'status' => 0,
                'noadult' => $_POST['noadult'],
                'noch' => $_POST['noch'],
                'noperson' => $_POST['persons'],
                'chprice' => $_POST['chprice'],
                'adultprice' => $_POST['adultprice'],
                'totalprice' => $_POST['totalprice'],
                'source_info' => $_POST['s_b_i'],
                'pickup_location' => $_POST['pickup_location'],
                'note' => $_POST['note'],
            );

            if ( $this->db->insert ( 'excursionbooking', $data ) )
            {
                $newid = $this->db->insert_id ();
                return array ('success' => 'success', 'booking_id' => $newid);
            }
            else
            {
                return array ('success' => 'failed', 'message' => "database error");
            }
        }

        function booking_read ()
        {

            $data = array ();

            //print_r($newid);
            $data['eb_newid'] = $_GET['fbid'];

            //podaci o bookingu
            $query = $this->db->get_where ( 'excursionbooking', array ('id' => $data['eb_newid']) )->result_array ();
            foreach ( $query as $key => $list )
            {
                $data['eb_excid'] = $list['excursions_id'];
                $data['eb_customerid'] = $list['customers_id'];
                $data['eb_date_from'] = $list['date_from'];
                $data['eb_num_of_day'] = $list['num_of_day'];
                $data['eb_adultprice'] = $list['adultprice'];
                $data['eb_chprice'] = $list['chprice'];
                $data['eb_totalprice'] = $list['totalprice'];
                $data['eb_status'] = $list['status'];
                $data['eb_noadult'] = $list['noadult'];
                $data['eb_noch'] = $list['noch'];
                $data['eb_noperson'] = $list['noperson'];
                $data['eb_userid'] = $list['userid'];
                $data['eb_note'] = $list['note'];
            }
            //Calculate SubToal
            $data['eb_adultprice_sub'] = $data['eb_noadult'] * $data['eb_adultprice'];
            $data['eb_chprice_sub'] = $data['eb_noch'] * $data['eb_chprice'];

            //podaci o customeru
            $query = $this->db->get_where ( 'customers', array ('id' => $data['eb_customerid']) )->result_array ();
            foreach ( $query as $key => $list )
            {
                $data['cust_firstName'] = $list['firstName'];
                $data['cust_lastName'] = $list['lastName'];
                $data['cust_email'] = $list['email'];
                $data['cust_phone'] = $list['phone'];
                $data['address'] = $list['address1'];
                $data['city'] = $list['town_city'];
                $data['zip'] = $list['zip_code'];
                $data['country'] = $list['country'];
            }

            //podaci o eskurziji
            $query = $this->db->get_where ( 'excursions', array ('id' => $data['eb_excid']) )->result_array ();
            foreach ( $query as $key => $list )
            {

                //Translate class
                $data['exc_title'] = $this->translate->getArray ( $list['title'], TRUE );

                $data['exc_startWeekDay'] = $list['startWeekDay'];
                $data['exc_excursion_text'] = $list['excursion_text'];

                //Translate class
                $data['exc_addition'] = $this->translate->getArray ( $list['addition'], TRUE );

                $data['exc_adultPrice'] = $list['adultPrice'];
                $data['exc_childPrice'] = $list['childPrice'];
                $data['exc_transportsid'] = $list['transportsid'];
                $data['exc_guides'] = $this->translate->getArray ( $list['guides'], TRUE );
                ;
            }

            //podaci o transportu
            $query = $this->db->get_where ( 'transports', array ('id' => $data['exc_transportsid']) )->result_array ();
            foreach ( $query as $key => $list )
            {
                $data['tra_title'] = $list['title'];
            }

            return $data;
        }

        function booking_read_tour ()
        {

            $data = array ();

            //print_r($newid);
            $data['eb_newid'] = $_GET['fbid'];

            //podaci o bookingu
            $query = $this->db->get_where ( 'tourbooking', array ('id' => $data['eb_newid']) )->result_array ();
            foreach ( $query as $key => $list )
            {
                $data['eb_excid'] = $list['tours_id'];
                $data['eb_customerid'] = $list['customers_id'];
                $data['eb_date_from'] = $list['date_from'];
                $data['eb_num_of_day'] = $list['num_of_day'];
                $data['eb_adultprice'] = $list['adultprice'];
                $data['eb_chprice'] = $list['chprice'];
                $data['eb_totalprice'] = $list['totalprice'];
                $data['eb_status'] = $list['status'];
                $data['eb_noadult'] = $list['noadult'];
                $data['eb_noch'] = $list['noch'];
                $data['eb_noperson'] = $list['noperson'];
                $data['eb_userid'] = $list['userid'];
                $data['eb_note'] = "Empty";
            }
            //Calculate SubToal
            $data['eb_adultprice_sub'] = $data['eb_noadult'] * $data['eb_adultprice'];
            $data['eb_chprice_sub'] = $data['eb_noch'] * $data['eb_chprice'];
            //podaci o customeru
            $query = $this->db->get_where ( 'customers', array ('id' => $data['eb_customerid']) )->result_array ();
            foreach ( $query as $key => $list )
            {
                $data['cust_firstName'] = $list['firstName'];
                $data['cust_lastName'] = $list['lastName'];
                $data['cust_email'] = $list['email'];
                $data['cust_phone'] = $list['phone'];
            }

            //podaci o turi
            $query = $this->db->get_where ( 'tours', array ('id' => $data['eb_excid']) )->result_array ();
            foreach ( $query as $key => $list )
            {

                //Translate class
                $data['exc_title'] = $this->translate->getArray ( $list['title'], TRUE );

                $data['exc_startWeekDay'] = $list['startWeekDay'];
                $data['exc_excursion_text'] = $list['tour_text'];

                //Translate class
                $data['exc_addition'] = $this->translate->getArray ( $list['addition'], TRUE );
            }

            return $data;
        }

        function action ( $action_type = 'action_1' )
        {

            $where = "status = 1 and " . $action_type . " = 1";

            $q = '
            SELECT excursions.*, gallery.path as g_path, pictures.filename as f_name
            FROM excursions
            LEFT JOIN gallery ON excursions.id = gallery.`excursions_id`
            LEFT JOIN pictures ON gallery.ID = pictures.gallery_ID
            AND pictures.ID=
            (
            SELECT pictures.ID
            FROM pictures
            WHERE pictures.gallery_ID = gallery.ID
            ORDER BY pictures.order ASC, pictures.ID ASC
            LIMIT 0, 1  
            )
            WHERE ' . $where . '
            ORDER BY adultPrice ASC
            ';

            $res = $this->db->query ( $q )->result_array ();

            log_message ( 'error', $this->db->last_query () );

            /* Translate */

            foreach ( $res as $key => $value )
            {
                foreach ( $this->fields as $f )
                {
                    $res[$key][$f] = $this->translate->getArray ( $value[$f], TRUE );
                    if ( $res[$key][$f] == '' )
                        $res[$key][$f] = '-Please translate-';
                }
            }

            return $res;
        }

        /* TRANSACTIONS AND INVOICE TILL END OF CLASS */

        function show_transaction ()
        {
//            log_message ( "error", "-------------- show_transaction -----------------" );
//
//            log_message ( "error", $_POST['trans_id'] );
//            log_message ( "error", "encode: " . urlencode ( $_POST['trans_id'] ) );
//            log_message ( "error", "decode: " . urldecode ( $_POST['trans_id'] ) );
//
//            log_message ( "error", "-----------------------------------" );

            $_POST['trans_id'] = urldecode ( $_POST['trans_id'] );


            $this->ibook = $this->load->database ( 'ibook', TRUE );
            $hotels_trans = $this->ibook->where ( 'trans_id', $_POST['trans_id'] )->get ( 'transaction' )->row_array ();


            log_message ( 'error', $this->db->last_query () );


            if ( count ( $hotels_trans ) > 0 )
            {
                return $hotels_trans;
            }
            else
            {
                $this->ibook = $this->load->database ( 'default', TRUE );
                return $this->db->get_where ( 'transaction', array ('trans_id' => $_POST['trans_id']) )->row_array ();
            }
        }

        function show_booking_id ()
        {

//            log_message ( "error", "-------------- show_booking_id -----------------" );
//
//            log_message ( "error", $_POST['trans_id'] );
//            log_message ( "error", "encode: " . urlencode ( $_POST['trans_id'] ) );
//            log_message ( "error", "decode: " . urldecode ( $_POST['trans_id'] ) );
//
//            log_message ( "error", "-----------------------------------" );

            $_POST['trans_id'] = urldecode ( $_POST['trans_id'] );

            if ( $_POST['type'] == "reservations" )
            {
                $this->ibook = $this->load->database ( 'ibook', TRUE );
                return $this->ibook->get_where ( 'reservations', array ('trans_id' => $_POST['trans_id']) )->row_array ();
            }
            else
            {
                return $this->db->get_where ( $_POST['type'], array ('trans_id' => $_POST['trans_id']) )->row_array ();
            }
        }

        function update_status ()
        {
            if ( $_POST['type'] == "reservations" )
            {
                $this->ibook = $this->load->database ( 'ibook', TRUE );
                $this->ibook->where ( 'ID', $_POST['ID'] )->update ( 'reservations', array (
                    'status' => $_POST['status'],
                    'voucher_sent' => time ()
                ) );

                if ( $this->input->post ( 'ENTER_PAYMENT' ) ):

//                    log_message('error',"ok");

                    $rows = $this->ibook
                            ->where ( 'reservations_ID', $_POST['ID'] )
                            ->count_all_results ( 'payments' );

//                    log_message ( 'error', "ok" . $rows );
//
                    if ( $rows == 0 ):

                        log_message ( 'error', "ok" . $rows );


                        $payment_data = array (
                            'reservations_ID' => $_POST['ID'],
                            'billing_type' => 'Credit card - Online authorisation',
                            'creation_date' => time (),
                            'amount' => $this->input->post ( 'AMOUNT' ),
                            'note' => 'NLB Montenegro payment gateway'
                        );

                        $this->ibook->insert ( 'payments', $payment_data );

                    endif;

                endif;
            }
            else
            {
                $this->db->where ( 'id', $_POST['id'] )->update ( $_POST['type'], array ('status' => $_POST['status']) );
            }

            //log_message("error", $this->db->last_query());

            if ( $this->db->affected_rows () >= 0 )
            {
                return TRUE;
            }
            else
            {
                return FALSE;
            }
        }

    }

?>
